package ssm.codegen.dao.jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.stereotype.Service;

import ssm.codegen.config.CodeGeneratorConfig;
import ssm.codegen.dao.DatabaseMetaDataDao;
import ssm.codegen.domain.MyTypes;
import ssm.codegen.util.TypeUtils;

/**
 * @author Jin,QingHua
 * @version build 2008.12
 */
@Service
public class DatabaseMetaDataDaoJdbcImpl extends NamedParameterJdbcDaoSupport implements DatabaseMetaDataDao {

	private final Logger logger = LoggerFactory.getLogger(this.getClass());

	@Resource
	private DataSource dataSource;

	@Resource(name = "codeGeneratorConfig")
	private CodeGeneratorConfig cfg;

	private String schemaPattern = null;

	private boolean isAllTables = false;

	@Override
	public List<String> selectTableNameList() throws DataAccessException {
		return this.selectTableNameList(false);
	}

	@Override
	@SuppressWarnings("unchecked")
	public List<String> selectTableNameList(boolean isIncludeAllTables) throws DataAccessException {
		isAllTables = isIncludeAllTables;
		return (List<String>) super.getNamedParameterJdbcTemplate().getJdbcOperations()
				.execute(new ConnectionCallback<Object>() {
					@Override
					public Object doInConnection(Connection connection) throws SQLException, DataAccessException {
						DatabaseMetaData dbMetaData = connection.getMetaData();
						logger.debug("{} -> {}", dbMetaData.getDatabaseProductName(),
								dbMetaData.getDatabaseProductVersion());

						List<String> tableNameList = new ArrayList<String>();
						String dbmsPorductName = StringUtils.upperCase(cfg.getDbmsPorductName());
						logger.debug("dbmsPorductName:{}", dbmsPorductName);
						String sql = "";
						ResultSet rs = null;
						if ("MYSQL".equals(dbmsPorductName)) { // MySQL
							// rs = dbMetaData.getTables(null, null, null, null);
							schemaPattern = ((BasicDataSource) dataSource).getUsername();
							logger.debug("schemaPattern:{}", schemaPattern);
							sql += "select t.TABLE_NAME from information_schema.`TABLES` t where t.TABLE_SCHEMA='";
							sql += schemaPattern;
							sql += "' and t.TABLE_TYPE='BASE TABLE' ";
							if (!isAllTables) {
								sql += cfg.getTableNameSqlConditions();
							}
							sql += " order by t.table_name";
						} else if ("ORACLE".equals(dbmsPorductName)) { // Oracle
							schemaPattern = ((BasicDataSource) dataSource).getUsername();
							sql += "select table_name from all_tables t where t.owner='" + schemaPattern + "' ";
							if (!isAllTables) {
								sql += cfg.getTableNameSqlConditions();
							}
							sql += " order by table_name";

						} else if ("MSSQLSERVER".equals(dbmsPorductName)) { // MS SQL SERVER
							sql += "select [name] as table_name from sysobjects where xtype='U' ";
							if (!isAllTables) {
								sql += cfg.getTableNameSqlConditions();
							}
							sql += " order by table_name";
						}
						logger.info("sql:{}", sql);
						rs = connection.createStatement().executeQuery(sql);

						String tableName = null;
						if (null == rs) {
							return null;
						}
						while (rs.next()) {
							tableName = rs.getString("TABLE_NAME").toUpperCase();
							logger.debug("[table]:{}", tableName);
							tableNameList.add(tableName);
						}
						if (null != rs) {
							rs.close();
						}

						return tableNameList;
					}
				});
	}

	@Override
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> selectColumnNameList(final String tableName) throws DataAccessException {
		return (List<Map<String, Object>>) super.getNamedParameterJdbcTemplate().getJdbcOperations()
				.execute(new ConnectionCallback<Object>() {
					@Override
					public Object doInConnection(Connection connection) throws SQLException, DataAccessException {
						DatabaseMetaData dbMetaData = connection.getMetaData();
						schemaPattern = ((BasicDataSource) dataSource).getUsername();

						Map<String, String> commentsMap = new HashMap<String, String>();
						if (StringUtils.equalsIgnoreCase("ORACLE", cfg.getDbmsPorductName())) {
							String sqlComments = "select a.column_name, comments as column_comment from all_tab_columns a, all_col_comments b "
									+ " where a.table_name = b.table_name "
									+ " and a.column_name = b.column_name and a.owner = b.owner and a.owner = '%s' and a.table_name = '%s'";
							sqlComments = String.format(sqlComments, schemaPattern, tableName);
							PreparedStatement pstmt = connection.prepareStatement(sqlComments);
							ResultSet rsc = pstmt.executeQuery(sqlComments);
							while (rsc.next()) {
								commentsMap.put(rsc.getString("column_name"), rsc.getString("column_comment"));
							}
							if (null != rsc) {
								rsc.close();
							}
							if (null != pstmt) {
								pstmt.close();
							}
						} else if (StringUtils.equalsIgnoreCase("MYSQL", cfg.getDbmsPorductName())) {
							String sqlComments = "SELECT t.COLUMN_NAME,t.COLUMN_COMMENT from information_schema.`COLUMNS` t "
									+ " where t.TABLE_SCHEMA='%s' and t.TABLE_NAME='%s'";
							sqlComments = String.format(sqlComments, schemaPattern, tableName);

							PreparedStatement pstmt = connection.prepareStatement(sqlComments);
							ResultSet rsc = pstmt.executeQuery(sqlComments);
							while (rsc.next()) {
								commentsMap.put(rsc.getString("column_name"), rsc.getString("column_comment"));
							}
							if (null != rsc) {
								rsc.close();
							}
							if (null != pstmt) {
								pstmt.close();
							}
						}

						ResultSet columns = dbMetaData.getColumns(null, schemaPattern, tableName, null);
						List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
						while (columns.next()) {
							Map<String, Object> map = new HashMap<String, Object>();

							String columnName = columns.getString("COLUMN_NAME").toUpperCase();
							map.put("columnName", columnName);
							logger.debug("[columnName]:{}", columnName);

							map.put("columnComments", commentsMap.get(columnName));
							logger.debug("[columnComments]:{}", commentsMap.get(columnName));

							String propertyName = columnName.toLowerCase();
							map.put("propertyName", propertyName);
							logger.debug("[propertyName]:{}", propertyName);

							int columnSize = columns.getInt("COLUMN_SIZE");
							map.put("columnSize", columnSize);
							logger.debug("[columnSize]:{}", columnSize);

							int decimalDigits = columns.getInt("DECIMAL_DIGITS");
							map.put("decimalDigits", decimalDigits);
							logger.debug("[decimalDigits]:{}", decimalDigits);

							Integer dataType = columns.getInt("DATA_TYPE");
							map.put("dataType", dataType);
							logger.debug("[dataType]:{}", dataType);

							MyTypes mts = TypeUtils.getTypes(dataType, columnSize, decimalDigits);

							String jdbcTypeName = mts.getJdbcTypeName();
							map.put("jdbcTypeName", jdbcTypeName);
							logger.debug("[jdbcTypeName]:{}", jdbcTypeName);

							String javaClassName = mts.getJavaClassName();
							map.put("javaClassName", javaClassName);
							logger.debug("[javaClassName]:{}", javaClassName);

							String javaClassSimpleName = StringUtils.substringAfterLast(javaClassName, ".");
							map.put("javaClassSimpleName", javaClassSimpleName);
							logger.debug("[javaClassSimpleName]:{}", javaClassSimpleName);

							list.add(map);
						}
						return list;
					}
				});
	}

}
